-- =============================================
-- This stored procedure deletes specified NavMenu and all if its descendants
-- as well as associated content.
--
-- Modifications
-- 07/11/2003    E.Tatsui
-- =============================================

CREATE                                PROCEDURE amsp_CMDeleteNavMenu
  @InNavMenuID numeric,
  @InContactID numeric,
  @InIgnorePrivilege char(1) = 'N',
  @OutErrorMessage varchar(255) OUTPUT
AS
BEGIN
  DECLARE
    @ContentID numeric,
    @WebsiteKey uniqueidentifier,
    @ParentNavMenuID numeric,
    @UnAuthorizedNum numeric,
    @NavMenuID numeric,
    @ContentCount int

  CREATE TABLE #NavMenuToDelete
  (NavMenuID numeric,
   ContentCount numeric,
   ContentAuthorityGroupID numeric,
   SortOrder numeric(28,18))

  -- Include the descendants as long as they have permission to delete this record.
  INSERT INTO #NavMenuToDelete
  SELECT a.NavMenuID,
         0,
         a.ContentAuthorityGroupID,
         a.SortOrder
    FROM Nav_Menu a WITH (NOLOCK)
   WHERE a.NavMenuID = @InNavMenuID

  WHILE @@RowCount > 0 BEGIN
    INSERT INTO #NavMenuToDelete
    SELECT a.NavMenuID,
           0,
           a.ContentAuthorityGroupID,
           a.SortOrder
      FROM Nav_Menu a WITH (NOLOCK)
     WHERE ParentNavMenuID IN (SELECT NavMenuID FROM #NavMenuToDelete)
       AND NavMenuID NOT IN (SELECT NavMenuID FROM #NavMenuToDelete)
  END

  -- Check to make sure the user has authorization to delete everything.
  IF @InIgnorePrivilege = 'N' BEGIN
    SELECT @UnAuthorizedNum = Count(*)
      FROM #NavMenuToDelete a LEFT OUTER JOIN Content_Authority_Producer b WITH (NOLOCK)
        ON a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
       AND b.NavCreatorFlag = 'Y'
       AND b.ContactID = @InContactID
     WHERE b.ContentAuthorityGroupID IS NULL

    IF @UnAuthorizedNum > 0 BEGIN
      SET @OutErrorMessage = 'You are not allowed to delete this item, because there are descendants of this item you don''t have privileges to delete.'
      RETURN
    END
  END

  -- Mark all the nav item as deleted.
  UPDATE Nav_Menu
     SET WorkflowStatusCode = 'D'
   WHERE NavMenuID IN (SELECT NavMenuID FROM #NavMenuToDelete)

  -- Request each content record to be deleted.
  DECLARE cContentToDelete CURSOR FOR
  SELECT a.ContentID
    FROM vCurrent_Content a WITH (NOLOCK), #NavMenuToDelete b
   WHERE a.NavMenuID = b.NavMenuID
   ORDER BY b.SortOrder

  OPEN cContentToDelete
  
  FETCH NEXT FROM cContentToDelete
   INTO @ContentID

  WHILE @@FETCH_STATUS = 0 BEGIN
    EXEC amsp_CMDeleteContent @ContentID, @InContactID, 'N', NULL
  
    FETCH NEXT FROM cContentToDelete
     INTO @ContentID
  END
  CLOSE cContentToDelete
  DEALLOCATE cContentToDelete
  -- Ends taking care of content records.

  UPDATE #NavMenuToDelete
     SET ContentCount = (SELECT COUNT(*) FROM vCurrent_Content WHERE NavMenuID = #NavMenuToDelete.NavMenuID)

  -- For nav item that doesn't have any current content records, let's just delete them now.
  DECLARE c_NavMenuToDelete CURSOR FOR
   SELECT NavMenuID, ContentCount
     FROM #NavMenuToDelete

  OPEN c_NavMenuToDelete
  FETCH NEXT FROm c_NavMenuToDelete
   INTO @NavMenuID,
        @ContentCount
  WHILE @@FETCH_STATUS = 0 BEGIN
    -- For both the items deleted and items requested to be deleted...
    -- Grab the parent parent, unless it's also being deletecd.    
    SET @ParentNavMenuID = NULL
    SELECT @ParentNavMenuID = ParentNavMenuID
      FROM Nav_Menu WITH (NOLOCK)
     WHERE NavMenuID = @NavMenuID
       AND ParentNavMenuID NOT IN (SELECT NavMenuID FROM #NavMenuToDelete)

    -- Do the rest only if we're deleting right nwo.
    IF @ContentCount = 0 BEGIN
        UPDATE Content
         SET NavMenuID = (SELECT NavMenuID FROM Nav_Menu WHERE Name = 'Content_Recycle_Bin')
       WHERE NavMenuID = @NavMenuID

      -- IF there are no published content associated with this NavMenuID, go ahead and
      -- delete the record.
      UPDATE Publish_Request
         SET NavMenuID = NULL
       WHERE NavMenuID = @NavMenuID
  
      DELETE FROM Nav_Menu_Workflow_Log WHERE NavMenuID = @NavMenuID
      DELETE FROM Nav_Menu_Feature WHERE NavMenuID = @NavMenuID
      DELETE FROM Nav_Menu_Security_Group WHERE NavMenuID = @NavMenuID
      DELETE FROM Nav_Menu_Setup_Log WHERE NavMenuID = @NavMenuID
      DELETE FROM Nav_Menu WHERE NavMenuID = @NavMenuID
    END

    -- If we set the parent earlier, update it to reflect the changes.
    IF @ParentNavMenuID IS NOT NULL
      EXEC amsp_CMUpdateNavProperties @ParentNavMenuID

    FETCH NEXT FROm c_NavMenuToDelete
     INTO @NavMenuID, @ContentCount
  END
  CLOSE c_NavMenuToDelete
  DEALLOCATE c_NavMenuToDelete   
  
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMDeleteNavMenu] TO [IMIS]
GO